Homework 4

Python selenium, APIs, & Pandas Basics

Author

Byeong-Hak Choe

Published

April 16, 2025

Modified

April 16, 2025

Direction

  • Please submit your Python Script for Part 1 and Part 2 in Homework 4 to Brightspace with the name below:

    • danl_210_hw4_LASTNAME_FIRSTNAME.py
      ( e.g., danl_210_hw4_choe_byeonghak.py )

  • The due is April 18, 2025, 10:30 A.M.

  • Please send Prof. Choe an email (bchoe@geneseo.edu) if you have any questions.


Part 1. Data Collection

Question 1

Answer:



Question 2

The series_housing_price DataFrame contains FRED series ids for the Home Price Index across 16 metropolitan statistical areas, each categorized into three price tiers: low, middle, and high.

url = 'https://bcdanl.github.io/data/fred_api_series_housing_price.csv'
series_housing_price = pd.read_csv(url)


For more information regarding the index, please visit Standard & Poor’s. There is more information about home price sales pairs in the Methodology section. Copyright, 2016, Standard & Poor’s Financial Services LLC.

Provide a Python code (excluding your FRED API key) to construct the following DataFrame:


  • Note that the id variable in the series_housing_price DataFrame corresponds to the series_id key used in the parameter dictionary (param_dicts) used for making requests to the FRED API.

  • To iterate over the id values in the series_housing_price DataFrame, you can convert the variable to a list using .tolist():

for val in series_housing_price['id'].tolist():
   # do something with val
  • Below replaces ‘Home Price Index (’ with ’’ (empty string) in the title column:
df_all['title'] = df_all['title'].str.replace('Home Price Index (', '')
  • The title column can split into the tier and city variables using the following code:
# Split the 'title' column into two new variables, 'ranking' and 'title'
# by the first occurrence of " Tier) for ". 

# expand=True tells pandas to return the results in a DataFrame
  # If expand were False, the result would be a Series of lists.

# Use \) to escape the closing parenthesis, which has special meaning in regex
df_all[['tier', 'city']] = df_all['title'].str.split(' Tier\) for ', expand=True)
  • The DataFrame is sorted first by state, then by city within each state, then by date in descending order within each city, and finally by tier within each city-date group.
    • To sort observations in a custom order (like Low, Middle, High), you can convert the tier variable to a categorical variable with an explicit order, and then sort.
df = pd.DataFrame({
    'tier': ['Middle', 'High', 'Low', 'Middle', 'Low'],
    'city': ['NY', 'LA', 'CHI', 'SF', 'SEA']
})

tier_order = ['Low', 'Middle', 'High']
df['tier'] = pd.Categorical(df['tier'], categories=tier_order, ordered=True)

df_sorted = df.sort_values('tier')

Answer:




Part 2. Pandas Basics

import pandas as pd
import numpy as np

The following describes the context of DataFrame in Part 2.

Open Payments, which is managed by the Centers for Medicare & Medicaid Services (CMS), is a national disclosure program created by the Affordable Care Act (ACA). The program promotes transparency and accountability by helping consumers understand the financial relationships between pharmaceutical and medical device industries, physicians, and non-physician practitioners (NPP). Non-physician practitioners (NPPs) collectively refers to physician assistants, nurse practitioners, clinical nurse specialists, certified registered nurse anesthetists/anesthesiologists and certified nurse-midwives provider types.

These financial relationships may include consulting fees, research grants, travel reimbursements, and payments made from the industry to medical practitioners. It is important to note that financial ties between the health care industry and health care providers do not necessarily indicate an improper relationship.

  • Let us consider the CMS’ Open Payments data for selected cities in New York state in year 2022, the cms_ny_2022 DataFrame:
cms_ny_2022 = pd.read_csv('https://bcdanl.github.io/data/cms-2022-cities-all.zip')

Variable Description

variable type description
Record_ID numeric Unique identifier for a transaction between a manufacturer and a physician or an NPP
Month numeric Month
Day numeric Year
Amount_of_Payment numeric US dollar amount of payment to a physician or NPP
Product chracter An indicator showing if the product is a Drug, Device, Biological, or Medical Supply.
Product_Manufacturer chracter Manufacturer making the payment to a physician/NPP
NPI numeric National Provider Identifier, a unique identification number for a physician/NPP receiving the payment
Physician_or_NPP chracter An indicator showing if the recipient of the payment is a physician or non-physician practitioner (NPP)
First_Name chracter First name of physician/NPP in New York state
Last_Name chracter Last name of physician/NPP in New York state
City chracter City of the physician/NPP in New York state
Type chracter Type of physician/NPP (e.g., Medical Doctor, Doctor of Optometry, Nurse Practitioner, Physician Assistant, and more)
Taxonomy chracter Standardized taxonomy of medical service provider (e.g., Allopathic & Osteopathic Physicians, Chiropractic Providers, Dental Providers, Physician Assistants & Advanced Practice Nursing Providers, and more)
Specialty chracter A specialty chosen within the standardized taxonomy (e.g., Psychiatry & Neurology, Physician Assistant, Chiropractor, Dentist, Family Medicine, Neurological Surgery, and more)
Specialty_Detail chracter Detail in the specialty if any (e.g., Family, Gastroenterology, Cardiovascular Disease, Neurology, Psychiatry, and more)
  • Note that each physician/NPP corresponds to one single Specialty_Detail value.

Question 3

  • Write a Python code to convert the string type Physician_or_NPP variable into the category type variable.


Question 4

  • Write a Python code to replace the NaN value of the Specialty_Detail variable with the value of the Specialty variable.
    • For example, the following shows that
      • All NaN values in the Specialty_Detail variable, where the Specialty is Physician Assistant, are replaced with Physician Assistant.

Answer:


Question 5

Write a Python code to count the number of transactions in which the payment amount (i.e., Amount_of_Payment) falls in the top 10% of all payments in the DataFrame.

Answer:


Question 6

Write a Python code to identify the city or cities with the highest number of non-missing values in the Specialty_Detail variable of the cms_ny_2022 DataFrame.

Answer:


Question 7

  • Write a Python code to count the number of occurrences of each unique value in Specialty_Detail for each unique value in Taxonomy.
    • For example, the following displays the number of occurrences of each unique in Specialty_Detail for the Dental Providers Taxonomy value.

Answer:


Question 8

  • Write a Python code to count the number of unique Specialty_Detail values for the value “Dental Providers” in Taxonomy.

Answer:


Question 9

  • Write a Python code (1) to count the number of missing values and (2) to calculate mean, standard deviation, minimum, first quartile, median, third quartile, and maximum of the Amount_of_Payment variable for the value Dermatology in the Specialty variable.

Answer:


Question 10

  • Write a Python code to replicate the following DataFrame, which calculates (1) the number of physicians in each city, (2) the number of NPPs in each city, (3) the number of physicians and NPPs in each city, and (4) the percentage of physicians and NPPs in each city, as shown below.
    • Ensure that the sum of the percentages of physicians and NPPs within each city is 100.
    • Ensure that the observations are sorted by Total_Count and City in descending and ascending orders, respectively.

Answer:


Question 11

  • Write a Python code to calculate:
    • The total Amount_of_Payment the Product_Manufacturer AbbVie Inc. paid to physicians and non-physician practitioners (NPPs) in Cities of Rochester, Buffalo, and Syracuse.
    • How many times the Product_Manufacturer AbbVie Inc. paid to physicians and non-physician practitioners (NPPs) in in Cities of Rochester, Buffalo, and Syracuse.

Answer:


Question 12

  • Consider the following two DataFrames, cms_ny_2022_npi and cms_ny_2022_records:
cms_ny_2022_npi <- pd.read_csv('https://bcdanl.github.io/data/cms-2022-cities-npi.csv')
  • cms_ny_2022_npi: This DataFrame is created from cms_ny_2022 by selecting only the columns NPI, Physician_or_NPP, First_Name, Last_Name, City, Type, Taxonomy, Specialty, and Specialty_Detail.
  • In addition, it contains only unique observations (i.e., there are no duplicate rows).
cms_ny_2022_records <- pd.read_csv('https://bcdanl.github.io/data/cms-2022-cities-records.csv')

  • cms_ny_2022_records: This DataFrame is derived from cms_ny_2022 by including every observation but only retaining the columns Record_ID, Month, Day, Amount_of_Payment, Product, Product_Manufacturer, and NPI.

  • Write a Python code to create the cms_ny_2022 DataFrame by using the cms_ny_2022_npi and cms_ny_2022_records DataFrames.

Answer:


Question 13

  • Write a Python code to create the following DataFrame, City_Physician_or_NPP, which counts the number of physicians and the non-physician practitioners (NPPs) in each city.

Answer:

Back to top